package mo.org.cm309.shoppingcart.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import mo.org.cm309.shoppingcart.entity.Product;


import org.apache.log4j.Logger;

public class ProductDAO extends BaseDAO {

	private static ProductDAO instance;

	static Logger log = Logger.getLogger(ProductDAO.class);
	public ProductDAO() {
		super();
	}

	public static synchronized ProductDAO getInstance() {
		log.info("Get Connection.");
		if (instance == null) {
			instance = new ProductDAO();
		}
		return instance;
	}

	// INSERT A PRODUCT
	// INPUT : PRODUCT (OBJECT)
	// OUTPUT: SUCCESS: PRODUCTID (LONG)
	// FAIL : -1 (LONG)
	public long insert(Product product) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			conn.setAutoCommit(false);
			String sql = "insert into product(productname, category, brand, supplier, spec1, " +
						 "spec2, spec3, spec4, spec5, spec6, spec7, spec8, spec9, spec10, desc, " +
						 "price,discount, stock, sold, image) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?," +
						 "?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, product.getProductname());
			ps.setString(2, product.getCategory());
			ps.setString(3, product.getBrand());
			ps.setString(4, product.getSupplier());
			ps.setString(5, product.getSpec1());
			ps.setString(6, product.getSpec2());
			ps.setString(7, product.getSpec3());
			ps.setString(8, product.getSpec4());
			ps.setString(9, product.getSpec5());
			ps.setString(10, product.getSpec6());
			ps.setString(11, product.getSpec7());
			ps.setString(12, product.getSpec8());
			ps.setString(13, product.getSpec9());
			ps.setString(14, product.getSpec10());
			ps.setString(15, product.getDesc());
			ps.setDouble(16, product.getPrice());
			ps.setDouble(17, product.getDiscount());
			ps.setLong(18, product.getStock());
			ps.setLong(19, product.getSold());
			ps.setBlob(20, bytes2Blob(product.getImage()));
			log.info(sql);
			ps.executeUpdate();
			ResultSet lastId = conn.createStatement().executeQuery(
					"select last_insert_id() as ID from user limit 1");
			if (lastId.next()) {
				product.setId(lastId.getLong("ID"));
			}
			conn.commit();
			return product.getId();

		} catch (SQLException ex) {
			if (conn != null) {
				try {
					conn.rollback();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			ex.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				log.error(e);
			}
			releaseConnection(conn);
		}
		return -1;
	}

	// UPDATE A USER
	// INPUT : USER (OBJECT)
	public void update(Product product) {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "";
		try {
			conn = getConnection();
			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			conn.setAutoCommit(false);
			sql = "update product set productname=?, category =?, "
					+ "brand=?, supplier=?, spec1=?, spec2=?, spec3=?, "
					+ "spec4=?, spec5=?, spec6=?, spec7=?, spec8=?, spec9=?, spec10=?, "
					+ "desc=?,  price=?, discount=?, stock=?, sold=?, image=? "
					+ "where id=? ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, product.getProductname());
			ps.setString(2, product.getCategory());
			ps.setString(3, product.getBrand());
			ps.setString(4, product.getSupplier());
			ps.setString(5, product.getSpec1());
			ps.setString(6, product.getSpec2());
			ps.setString(7, product.getSpec3());
			ps.setString(8, product.getSpec4());
			ps.setString(9, product.getSpec5());
			ps.setString(10, product.getSpec6());
			ps.setString(11, product.getSpec7());
			ps.setString(12, product.getSpec8());
			ps.setString(13, product.getSpec9());
			ps.setString(14, product.getSpec10());
			ps.setString(15, product.getDesc());
			ps.setDouble(16, product.getPrice());
			ps.setDouble(17, product.getDiscount());
			ps.setLong(18, product.getStock());
			ps.setLong(19, product.getSold());
			ps.setBlob(20, bytes2Blob(product.getImage()));
			ps.setLong(21, product.getId());
			log.info(sql);
			ps.executeUpdate();
			conn.commit();
			log.info("Update Product Item Successful.");
		} catch (SQLException ex) {
			try {
				conn.rollback();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				log.error(e);
			}
			log.error(ex);
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				log.error(e);
			}
			releaseConnection(conn);
		}

	}
	
	// UPDATE A USER
	// INPUT : USER (OBJECT)
	public void updateWithOutImage(Product product) {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "";
		try {
			conn = getConnection();
			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			conn.setAutoCommit(false);
			sql = "update product set productname=?, category =?, "
					+ "brand=?, supplier=?, spec1=?, spec2=?, spec3=?, "
					+ "spec4=?, spec5=?, spec6=?, spec7=?, spec8=?, spec9=?, spec10=?, "
					+ "desc=?,  price=?, discount=?, stock=?, sold=? "
					+ "where id=? ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, product.getProductname());
			ps.setString(2, product.getCategory());
			ps.setString(3, product.getBrand());
			ps.setString(4, product.getSupplier());
			ps.setString(5, product.getSpec1());
			ps.setString(6, product.getSpec2());
			ps.setString(7, product.getSpec3());
			ps.setString(8, product.getSpec4());
			ps.setString(9, product.getSpec5());
			ps.setString(10, product.getSpec6());
			ps.setString(11, product.getSpec7());
			ps.setString(12, product.getSpec8());
			ps.setString(13, product.getSpec9());
			ps.setString(14, product.getSpec10());
			ps.setString(15, product.getDesc());
			ps.setDouble(16, product.getPrice());
			ps.setDouble(17, product.getDiscount());
			ps.setLong(18, product.getStock());
			ps.setLong(19, product.getSold());
			ps.setLong(20, product.getId());
			log.info(sql);
			ps.executeUpdate();
			conn.commit();
			log.info("Update Product Item Successful.");
		} catch (SQLException ex) {
			try {
				conn.rollback();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				log.error(e);
			}
			log.error(ex);
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				log.error(e);
			}
			releaseConnection(conn);
		}

	}

	// DELERE A PRODUCT
	// INPUT : PRODUCT (OBJECT, JUST ONLY INCLUDE PRODUCTID)
	public void delete(Product product) {
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = getConnection();
			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			conn.setAutoCommit(false);
			String sql = "delete from product where id=? ";
			ps = conn.prepareStatement(sql);
			ps.setLong(1, product.getId());
			log.info(sql);
			ps.executeUpdate();
			conn.commit();
			log.info("Deleted Product : " + product.getId());
		} catch (SQLException ex) {
			try {
				conn.rollback();
			} catch (SQLException e) {
				log.error(e);
			}
			log.error(ex);
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				log.error(e);
			}
			releaseConnection(conn);
		}
	}

	// FIND SOME PRODUCTS
	// INPUT : WHERECLAUSE (STRING), ORDERCLAUSE (STRING)
	// OUTPUT: LIST OF PRODUCT (OBJECT)
	public List<Product> findByWhereOrderClause(String whereClause,
			String orderClause) {
		List<Product> list = new ArrayList<Product>();
		Connection conn 	 = null;
		PreparedStatement ps = null;
		ResultSet rs 		 = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(true);
			String sql = "select * from product " + whereClause + " "
					+ orderClause;
			log.info(sql);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Product p = new Product();
				p.setId(rs.getLong("id"));
				p.setProductname(rs.getString("productname"));
				p.setCategory(rs.getString("category"));
				p.setBrand(rs.getString("brand"));
				p.setSupplier(rs.getString("supplier"));
				p.setSpec1(rs.getString("spec1"));
				p.setSpec2(rs.getString("spec2"));
				p.setSpec3(rs.getString("spec3"));
				p.setSpec4(rs.getString("spec4"));
				p.setSpec5(rs.getString("spec5"));
				p.setSpec6(rs.getString("spec6"));
				p.setSpec7(rs.getString("spec7"));
				p.setSpec8(rs.getString("spec8"));
				p.setSpec9(rs.getString("spec9"));
				p.setSpec10(rs.getString("spec10"));
				p.setDesc(rs.getString("desc"));
				p.setPrice(rs.getDouble("price"));
				p.setDiscount(rs.getDouble("discount"));
				p.setStock(rs.getInt("stock"));
				p.setSold(rs.getInt("sold"));
				p.setImage(image2ByteArray(rs.getBlob("image")));
				list.add(p);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			releaseConnection(conn);
		}

		return list;
	}

	// FIND SOME PRODUCTS
	// INPUT : WHERECLAUSE (STRING)
	// OUTPUT: LIST OF PRODUCT (OBJECT)
	public List<Product> findByWhereClause(String whereClause) {
		return findByWhereOrderClause(whereClause, "");
	}

	// FIND ALL PRODUCTS THAT EXIST IN DATABASE
	// INPUT : NOTHING
	// OUTPUT: LIST OF PRODUCT (OBJECT)
	public List<Product> findAll() {
		return findByWhereClause("");
	}

	// FIND A PRODUCT
	// INPUT : PRODUCT ID (LONG)
	// OUTPUT: PRODUCT (OBJECT)
	public Product findById(long id) {
		List<Product> us = findByWhereOrderClause("where id=" + id, "order by id");
		if (us.size() > 0)
			return (Product) us.get(0);
		return null;
	}

	// FIND A USER
	// INPUT : EMAIL (STRING)
	// OUTPUT: USER (OBJECT)
//	public Product findByEmail(String email) {
//		List<User> us = findByWhereOrderClause("where email='" + email + "'",
//				"order by id");
//		if (us.size() > 0)
//			return (User) us.get(0);
//		return null;
//	}

}
